package rgbos.forum.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.Vector;
import rgbos.forum.domain.Auditor;
import rgbos.forum.domain.Post;

public class SQLPost{
	SqlTopicPost util_;
	
	public SQLPost(){
		util_ = new SqlTopicPost();
	}
	public int saveNewPost(Post post,int topicId) {
		if (util_.checkExistPost(post.getId_())){
			Auditor.Log("The post " + post.getId_() + " was ALLREADY added to the forum.", this.getClass());
    		return -1;
		}
		if (!util_.checkExistTopic(topicId)){
			Auditor.Log("The topic " + topicId + " wasn't added to the forum.", this.getClass());
    		return -1;
		}

		Statement st = null;
		int val = 0;
		int id = -1;
		try {
			st = SQLUtil.conn_.createStatement();
			//int id = post.getId_();
			String title = post.getTitle_();
			String authorName = post.getAuthorName();
			Timestamp creationDate = new Timestamp(post.getCreationDate_().getTime());
//			System.out.println(creationDate.toGMTString());
			String body = post.getBody_();

			val = st.executeUpdate("INSERT INTO posts (title,authorName,creationDate,body) "+
					" VALUES ('" + title + "','" + authorName + "','" + creationDate + "','"+ body +"')", Statement.RETURN_GENERATED_KEYS);

			ResultSet rs = st.getGeneratedKeys();


		    if (rs.next()) {
		        id = rs.getInt(1);
		    } else {
		    }
			st.executeUpdate("INSERT INTO topicsPosts (postId,topicId) VALUES(" + id + "," + topicId +")");

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	    if ( val > 0 ){
	    	Auditor.Log("The post " + post.getId_() + " was added to the forum.", this.getClass());
	    	return id;
	    }
	    else return -1;
	}
	public boolean modifyPost(Post post) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();

			int id = post.getId_();
			String title = post.getTitle_();
			String authorName = post.getAuthorName();
			//Date creationDate = new Date(post.getCreationDate_().getTime());
			String body = post.getBody_();

			int rs = stmt.executeUpdate
	        ("UPDATE posts SET title='"+title+"',authorName='"+authorName+
	         "',body='"+body+"' WHERE id="+id);
			if (rs >0){
				Auditor.Log("The post " + post.getId_()+"was successfully modified", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
        return false;
	}
	public boolean modifyPostBody(int postId,String title,String body) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE posts SET body='"+body+"'"+",title='"+title+"' WHERE id="+postId);
			if (rs  > 0){
				Auditor.Log("Postid: "+postId +" was modified his body", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The post " + postId +" isn\'t exist ", this.getClass());
        return false;
	}
	public boolean deletePost(int postId) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			stmt.executeUpdate("DELETE FROM topicsPosts WHERE postId = "+postId);
			int rs = stmt.executeUpdate("DELETE FROM posts WHERE id = "+postId);
	        if (rs >0){
        		Auditor.Log("The post " + postId + " was deleted from the forum.", this.getClass());
        		return true;
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The post " + postId + " was not in the Forum.", this.getClass());
		return false;
	}
	public Post getPostById(int postId){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM posts WHERE id="+postId);
	        while (rs.next()){
	        	Post post = new Post(rs.getInt("id"), rs.getString("title"),
	        	rs.getString("authorName"), new Date(rs.getTimestamp("creationDate").getTime()),rs.getString("body"));
//	        	System.out.println(post.getCreationDate_().toString());
	        	return post;
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
		return null;
	}
	public Vector<Post> getPosts() {
		Vector<Post> result = new Vector<Post>();
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM posts");
	        while (rs.next()){
	        	Post post = new Post(rs.getInt("id"), rs.getString("title"),
       			rs.getString("authorName"), new Date(rs.getTimestamp("creationDate").getTime()),rs.getString("body"));
//	        	System.out.println(post.getCreationDate_().toGMTString());
	        	result.add(post);
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
        return result;
	}
	public Vector<Post> getPostsByUser(String username) {
		Vector<Post> allPosts = getPosts();
		Vector<Post> resultPost = new Vector<Post>();
		for (Post post :allPosts){
			if (post.getAuthorName().equals(username)){
				resultPost.add(post);
				continue;
			}
		}
		return resultPost;
	}

}
